

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=auto>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.png">
  <link rel="icon" href="/img/favicon.png">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=5.0, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="author" content="Cheney">
  <meta name="keywords" content="Coding">
  
    <meta name="description" content="常用SQL语句">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL语句练习">
<meta property="og:url" content="https://cheney822.gitee.io/2021/06/01/%E6%95%B0%E6%8D%AE%E5%BA%93__SQL%E8%AF%AD%E5%8F%A5%E7%BB%83%E4%B9%A0/index.html">
<meta property="og:site_name" content="Cheney blog">
<meta property="og:description" content="常用SQL语句">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://imgbed.cheney.cc/picgo/20210702215127885.jpeg">
<meta property="article:published_time" content="2021-06-01T00:22:00.000Z">
<meta property="article:modified_time" content="2022-04-05T13:14:29.471Z">
<meta property="article:author" content="Cheney">
<meta property="article:tag" content="数据库">
<meta property="article:tag" content="SQL">
<meta property="article:tag" content="练习">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://imgbed.cheney.cc/picgo/20210702215127885.jpeg">
  
  
  <title>SQL语句练习 - Cheney blog</title>

  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/github-markdown-css@4/github-markdown.min.css" />
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/hint.css@2/hint.min.css" />

  
    
    
      
      <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/highlight.js@10/styles/github-gist.min.css" />
    
  

  
    <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.css" />
  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_ba1fz6golrf.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_lbnruvf0jn.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    var CONFIG = {"hostname":"cheney822.gitee.io","root":"/","version":"1.8.14","typing":{"enable":true,"typeSpeed":70,"cursorChar":"_","loop":false},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"right","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":false,"trickleSpeed":100}},"copy_btn":true,"image_zoom":{"enable":true,"img_url_replace":["",""]},"toc":{"enable":true,"headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":1},"lazyload":{"enable":true,"loading_img":"/img/loading.gif","onlypost":false,"offset_factor":2},"web_analytics":{"enable":false,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":null,"app_key":null,"server_url":null,"path":"window.location.pathname","ignore_local":false}},"search_path":"/local-search.xml"};
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
<meta name="generator" content="Hexo 5.4.1"></head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand" href="/">
      <strong>Cheney Blog</strong>
    </a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" target="_self" href="javascript:;" data-toggle="modal" data-target="#modalSearch" aria-label="Search">
              &nbsp;<i class="iconfont icon-search"></i>&nbsp;
            </a>
          </li>
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" target="_self" href="javascript:;" aria-label="Color Toggle">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="banner" id="banner" parallax=true
         style="background: url('https://imgbed.cheney.cc/Blog_config/default.png') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="page-header text-center fade-in-up">
            <span class="h2" id="subtitle" title="SQL语句练习">
              
            </span>

            
              <div class="mt-3">
  
  
    <span class="post-meta">
      <i class="iconfont icon-date-fill" aria-hidden="true"></i>
      <time datetime="2021-06-01 08:22" pubdate>
        2021年6月1日 早上
      </time>
    </span>
  
</div>

<div class="mt-1">
  
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      19k 字
    </span>
  

  
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      95 分钟
    </span>
  

  
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div class="py-5" id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">SQL语句练习</h1>
            
            <div class="markdown-body">
              <h2 id="数据库和数据表操作"><a href="#数据库和数据表操作" class="headerlink" title="数据库和数据表操作"></a>数据库和数据表操作</h2><h3 id="1-采用可视化软件建立数据库UNIVERSITY，其中包括六个数据表"><a href="#1-采用可视化软件建立数据库UNIVERSITY，其中包括六个数据表" class="headerlink" title="1.采用可视化软件建立数据库UNIVERSITY，其中包括六个数据表"></a>1.采用可视化软件建立数据库UNIVERSITY，其中包括六个数据表<img src="https://imgbed.cheney.cc/picgo/20210702215127885.jpeg" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></h3><h3 id="2-采用SQL语言删除1中建立的数据库和数据表"><a href="#2-采用SQL语言删除1中建立的数据库和数据表" class="headerlink" title="2.采用SQL语言删除1中建立的数据库和数据表"></a>2.采用SQL语言删除1中建立的数据库和数据表</h3><figure class="highlight sql"><table><tr><td class="gutter"><div class="code-wrapper"><pre><span class="line">1</span><br></pre></div></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">drop</span> database university;<br></code></pre></td></tr></table></figure>



<h3 id="3-采用SQL语言建立数据库UNIVERSITY"><a href="#3-采用SQL语言建立数据库UNIVERSITY" class="headerlink" title="3.采用SQL语言建立数据库UNIVERSITY"></a>3.采用SQL语言建立数据库UNIVERSITY</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> DATABASE UNIVERSITY;<br></code></pre></td></tr></table></figure>




<h3 id="4-采用SQL语言创建UNIVERSITY数据库中的六个数据表"><a href="#4-采用SQL语言创建UNIVERSITY数据库中的六个数据表" class="headerlink" title="4.采用SQL语言创建UNIVERSITY数据库中的六个数据表"></a>4.采用SQL语言创建UNIVERSITY数据库中的六个数据表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Department ( Dno <span class="hljs-type">INT</span>, Dname <span class="hljs-type">VARCHAR</span> ( <span class="hljs-number">50</span> ), Daddress <span class="hljs-type">VARCHAR</span> ( <span class="hljs-number">50</span> ), <span class="hljs-keyword">PRIMARY</span> KEY ( Dno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Student (Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ),Sname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ),Ssex <span class="hljs-type">CHAR</span> ( <span class="hljs-number">2</span> ),Sage <span class="hljs-type">INT</span>,Dno <span class="hljs-type">INT</span>,<span class="hljs-keyword">PRIMARY</span> KEY ( Sno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Dno ) <span class="hljs-keyword">REFERENCES</span> department ( Dno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Teacher (Tno <span class="hljs-type">INT</span> <span class="hljs-keyword">PRIMARY</span> KEY,Tname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ),Ttitle <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ),Dno <span class="hljs-type">INT</span>,<span class="hljs-keyword">FOREIGN</span> KEY ( Dno ) <span class="hljs-keyword">REFERENCES</span> department ( Dno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> course ( Cno <span class="hljs-type">INT</span> <span class="hljs-keyword">PRIMARY</span> KEY, Cname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">50</span> ), Cpno <span class="hljs-type">INT</span>, CCredit <span class="hljs-type">INT</span>, <span class="hljs-keyword">FOREIGN</span> KEY ( Cpno ) <span class="hljs-keyword">REFERENCES</span> Course ( Cno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> SC (Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ),Cno <span class="hljs-type">INT</span>,Grade <span class="hljs-type">INT</span>,<span class="hljs-keyword">PRIMARY</span> KEY ( Sno, Cno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Sno ) <span class="hljs-keyword">REFERENCES</span> Student ( Sno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Cno )<span class="hljs-keyword">REFERENCES</span> Course ( Cno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> TC (Tno <span class="hljs-type">INT</span>,Cno <span class="hljs-type">INT</span>,Site <span class="hljs-type">CHAR</span> ( <span class="hljs-number">50</span> ),<span class="hljs-keyword">PRIMARY</span> KEY ( Tno, Cno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Tno ) <span class="hljs-keyword">REFERENCES</span> Teacher ( Tno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Cno ) <span class="hljs-keyword">REFERENCES</span> Course ( Cno ) );<br></code></pre></td></tr></table></figure>





<h3 id="5-采用SQL语言为Student表的Sname建立唯一索引"><a href="#5-采用SQL语言为Student表的Sname建立唯一索引" class="headerlink" title="5.采用SQL语言为Student表的Sname建立唯一索引"></a>5.采用SQL语言为Student表的Sname建立唯一索引</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">UNIQUE</span> INDEX stuname <span class="hljs-keyword">ON</span> Student ( Sname );<br></code></pre></td></tr></table></figure>







<h3 id="6-采用SQL语言删除Student表Sname上的唯一索引"><a href="#6-采用SQL语言删除Student表Sname上的唯一索引" class="headerlink" title="6.采用SQL语言删除Student表Sname上的唯一索引"></a>6.采用SQL语言删除Student表Sname上的唯一索引</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> student <span class="hljs-keyword">DROP</span> INDEX stuname；<br></code></pre></td></tr></table></figure>






<h3 id="7-采用SQL语言给Teacher表添加一个字段Tsex"><a href="#7-采用SQL语言给Teacher表添加一个字段Tsex" class="headerlink" title="7.采用SQL语言给Teacher表添加一个字段Tsex"></a>7.采用SQL语言给Teacher表添加一个字段Tsex</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> Teacher <span class="hljs-keyword">ADD</span> Tsex <span class="hljs-type">CHAR</span> ( <span class="hljs-number">1</span> );<br></code></pre></td></tr></table></figure>






<h3 id="8-采用SQL语言删除Teacher表中的字段Tsex"><a href="#8-采用SQL语言删除Teacher表中的字段Tsex" class="headerlink" title="8.采用SQL语言删除Teacher表中的字段Tsex"></a>8.采用SQL语言删除Teacher表中的字段Tsex</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> Teacher <span class="hljs-keyword">DROP</span> <span class="hljs-keyword">COLUMN</span> Tsex;<br></code></pre></td></tr></table></figure>






<hr>
<h2 id="数据表的数据操作"><a href="#数据表的数据操作" class="headerlink" title="数据表的数据操作"></a>数据表的数据操作</h2><h3 id="1-分别采用UI界面和SQL语言为UNIVERSITY的Department表输入数据"><a href="#1-分别采用UI界面和SQL语言为UNIVERSITY的Department表输入数据" class="headerlink" title="1.分别采用UI界面和SQL语言为UNIVERSITY的Department表输入数据"></a>1.分别采用UI界面和SQL语言为UNIVERSITY的Department表输入数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>, <span class="hljs-string">&#x27;地球科学学院&#x27;</span>, <span class="hljs-string">&#x27;主楼东&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2</span>, <span class="hljs-string">&#x27;资源学院&#x27;</span>, <span class="hljs-string">&#x27;主楼西&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">3</span>, <span class="hljs-string">&#x27;材化学院&#x27;</span>, <span class="hljs-string">&#x27;材化楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">4</span>, <span class="hljs-string">&#x27;环境学院&#x27;</span>, <span class="hljs-string">&#x27;文华楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">5</span>, <span class="hljs-string">&#x27;工程学院&#x27;</span>, <span class="hljs-string">&#x27;水工楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">6</span>, <span class="hljs-string">&#x27;地球物理学院&#x27;</span>, <span class="hljs-string">&#x27;物探楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">7</span>, <span class="hljs-string">&#x27;机械与电子信息学院&#x27;</span>, <span class="hljs-string">&#x27;教二楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">8</span>, <span class="hljs-string">&#x27;经济管理学院&#x27;</span>, <span class="hljs-string">&#x27;经管楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">9</span>, <span class="hljs-string">&#x27;外语学院&#x27;</span>, <span class="hljs-string">&#x27;北一楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">10</span>, <span class="hljs-string">&#x27;信息工程学院&#x27;</span>, <span class="hljs-string">&#x27;NULL&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">11</span>, <span class="hljs-string">&#x27; 数学与物理学院&#x27;</span>, <span class="hljs-string">&#x27;基委楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">12</span>, <span class="hljs-string">&#x27;珠宝学院&#x27;</span>, <span class="hljs-string">&#x27;珠宝楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">13</span>, <span class="hljs-string">&#x27;政法学院&#x27;</span>, <span class="hljs-string">&#x27;政法楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">14</span>, <span class="hljs-string">&#x27;计算机学院&#x27;</span>, <span class="hljs-string">&#x27;北一楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">15</span>, <span class="hljs-string">&#x27;远程与继续教育学院&#x27;</span>, <span class="hljs-string">&#x27;NULL&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">16</span>, <span class="hljs-string">&#x27;国际教育学院&#x27;</span>, <span class="hljs-string">&#x27;NULL&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">17</span>, <span class="hljs-string">&#x27;体育部&#x27;</span>, <span class="hljs-string">&#x27;体育馆&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">18</span>, <span class="hljs-string">&#x27;艺术与传媒学院&#x27;</span>, <span class="hljs-string">&#x27;艺传楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">19</span>, <span class="hljs-string">&#x27;马克思主义学院&#x27;</span>, <span class="hljs-string">&#x27;保卫楼&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`department` (`Dno`, `Dname`, `Daddress`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20</span>, <span class="hljs-string">&#x27;江城学院&#x27;</span>, <span class="hljs-string">&#x27;江城校区&#x27;</span>);<br></code></pre></td></tr></table></figure>




<h3 id="2-分别采用UI界面和SQL语言为UNIVERSITY的Student表输入数据"><a href="#2-分别采用UI界面和SQL语言为UNIVERSITY的Student表输入数据" class="headerlink" title="2.分别采用UI界面和SQL语言为UNIVERSITY的Student表输入数据"></a>2.分别采用UI界面和SQL语言为UNIVERSITY的Student表输入数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20081001197</span>, <span class="hljs-string">&#x27;李子聪&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">17</span>, <span class="hljs-number">5</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20081001266</span>, <span class="hljs-string">&#x27;蔡景学&#x27;</span>, <span class="hljs-string">&#x27;F&#x27;</span>, <span class="hljs-number">19</span>, <span class="hljs-number">5</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20081001888</span>, <span class="hljs-string">&#x27;赵林云&#x27;</span>, <span class="hljs-string">&#x27;F&#x27;</span>, <span class="hljs-number">20</span>, <span class="hljs-number">5</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20081003492</span>, <span class="hljs-string">&#x27;易家新&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">19</span>, <span class="hljs-number">5</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000231</span>, <span class="hljs-string">&#x27;吕岩&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">18</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000481</span>, <span class="hljs-string">&#x27;姜北&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">17</span>, <span class="hljs-number">5</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000863</span>, <span class="hljs-string">&#x27;孟飞&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">19</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000934</span>, <span class="hljs-string">&#x27;罗振俊&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">19</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000961</span>, <span class="hljs-string">&#x27;曾雪君&#x27;</span>, <span class="hljs-string">&#x27;F&#x27;</span>, <span class="hljs-number">18</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000983</span>, <span class="hljs-string">&#x27;巴翔&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">19</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091001175</span>, <span class="hljs-string">&#x27;周雷&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">18</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091001261</span>, <span class="hljs-string">&#x27;马欢&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">17</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091001384</span>, <span class="hljs-string">&#x27;陈亮&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">20</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091001598</span>, <span class="hljs-string">&#x27;王海涛&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">20</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091003085</span>, <span class="hljs-string">&#x27;袁恒&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">18</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091004391</span>, <span class="hljs-string">&#x27;颜容&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">19</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20101000199</span>, <span class="hljs-string">&#x27;孙中孝&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">18</span>, <span class="hljs-number">11</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20101000424</span>, <span class="hljs-string">&#x27;杨光&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">17</span>, <span class="hljs-number">11</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20101000481</span>, <span class="hljs-string">&#x27;张永强&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">16</span>, <span class="hljs-number">11</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20101000619</span>, <span class="hljs-string">&#x27;陈博&#x27;</span>, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-number">20</span>, <span class="hljs-number">11</span>);<br></code></pre></td></tr></table></figure>




<h3 id="3-分别采用UI界面和SQL语言为UNIVERSITY的Course表输入数据"><a href="#3-分别采用UI界面和SQL语言为UNIVERSITY的Course表输入数据" class="headerlink" title="3.分别采用UI界面和SQL语言为UNIVERSITY的Course表输入数据"></a>3.分别采用UI界面和SQL语言为UNIVERSITY的Course表输入数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>, <span class="hljs-string">&#x27;数据库原理&#x27;</span>, <span class="hljs-number">5</span>, <span class="hljs-number">4</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2</span>, <span class="hljs-string">&#x27;高等数学&#x27;</span>, <span class="hljs-keyword">NULL</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">3</span>, <span class="hljs-string">&#x27;信息系统&#x27;</span>, <span class="hljs-number">1</span>, <span class="hljs-number">2</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">4</span>, <span class="hljs-string">&#x27;操作系统&#x27;</span>, <span class="hljs-number">5</span>, <span class="hljs-number">4</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">5</span>, <span class="hljs-string">&#x27;数据结构&#x27;</span>, <span class="hljs-number">6</span>, <span class="hljs-number">4</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">6</span>, <span class="hljs-string">&#x27;C语言程序设计&#x27;</span>, <span class="hljs-keyword">NULL</span>, <span class="hljs-number">4</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">7</span>, <span class="hljs-string">&#x27;大学物理&#x27;</span>, <span class="hljs-keyword">NULL</span>, <span class="hljs-number">8</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">8</span>, <span class="hljs-string">&#x27;大学化学&#x27;</span>, <span class="hljs-keyword">NULL</span>, <span class="hljs-number">3</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">9</span>, <span class="hljs-string">&#x27;汇编语言&#x27;</span>, <span class="hljs-number">6</span>, <span class="hljs-number">2</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">10</span>, <span class="hljs-string">&#x27;软件工程&#x27;</span>, <span class="hljs-keyword">NULL</span>, <span class="hljs-number">2</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">11</span>, <span class="hljs-string">&#x27;空间数据库&#x27;</span>, <span class="hljs-number">1</span>, <span class="hljs-number">3</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">12</span>, <span class="hljs-string">&#x27;美国简史&#x27;</span>, <span class="hljs-keyword">NULL</span>, <span class="hljs-number">2</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">13</span>, <span class="hljs-string">&#x27;中国通史&#x27;</span>, <span class="hljs-keyword">NULL</span>, <span class="hljs-number">6</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">14</span>, <span class="hljs-string">&#x27;大学语文&#x27;</span>, <span class="hljs-keyword">NULL</span>, <span class="hljs-number">3</span>);<br></code></pre></td></tr></table></figure>


<h3 id="4-分别采用UI界面和SQL语言为UNIVERSITY的Teacher表输入数据"><a href="#4-分别采用UI界面和SQL语言为UNIVERSITY的Teacher表输入数据" class="headerlink" title="4.分别采用UI界面和SQL语言为UNIVERSITY的Teacher表输入数据"></a>4.分别采用UI界面和SQL语言为UNIVERSITY的Teacher表输入数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>, <span class="hljs-string">&#x27;何小峰&#x27;</span>, <span class="hljs-string">&#x27;副教授&#x27;</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2</span>, <span class="hljs-string">&#x27;刘刚才&#x27;</span>, <span class="hljs-string">&#x27;教授&#x27;</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">3</span>, <span class="hljs-string">&#x27;李星星&#x27;</span>, <span class="hljs-string">&#x27;教授&#x27;</span>, <span class="hljs-number">11</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">4</span>, <span class="hljs-string">&#x27;翁正平&#x27;</span>, <span class="hljs-string">&#x27;讲师&#x27;</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">5</span>, <span class="hljs-string">&#x27;李川川&#x27;</span>, <span class="hljs-string">&#x27;讲师&#x27;</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">6</span>, <span class="hljs-string">&#x27;王媛媛&#x27;</span>, <span class="hljs-string">&#x27;讲师&#x27;</span>, <span class="hljs-number">14</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">7</span>, <span class="hljs-string">&#x27;孔夏芳&#x27;</span>, <span class="hljs-string">&#x27;副教授&#x27;</span>, <span class="hljs-number">14</span>);<br></code></pre></td></tr></table></figure>





<h3 id="5-分别采用UI界面和SQL语言为UNIVERSITY的SC表输入数据"><a href="#5-分别采用UI界面和SQL语言为UNIVERSITY的SC表输入数据" class="headerlink" title="5.分别采用UI界面和SQL语言为UNIVERSITY的SC表输入数据"></a>5.分别采用UI界面和SQL语言为UNIVERSITY的SC表输入数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20081001197</span>, <span class="hljs-number">1</span>, <span class="hljs-number">79</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20081001266</span>, <span class="hljs-number">1</span>, <span class="hljs-number">97</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20081001888</span>, <span class="hljs-number">1</span>, <span class="hljs-number">60</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000481</span>, <span class="hljs-number">1</span>, <span class="hljs-number">78</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000863</span>, <span class="hljs-number">1</span>, <span class="hljs-number">98</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000863</span>, <span class="hljs-number">6</span>, <span class="hljs-number">90</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000934</span>, <span class="hljs-number">1</span>, <span class="hljs-number">89</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000934</span>, <span class="hljs-number">6</span>, <span class="hljs-number">90</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000961</span>, <span class="hljs-number">1</span>, <span class="hljs-number">85</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091000961</span>, <span class="hljs-number">6</span>, <span class="hljs-number">87</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20091003085</span>, <span class="hljs-number">1</span>, <span class="hljs-number">90</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20101000199</span>, <span class="hljs-number">1</span>, <span class="hljs-number">65</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20101000424</span>, <span class="hljs-number">11</span>, <span class="hljs-number">78</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `sc` (`Sno`, `Cno`, `Grade`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20101000481</span>, <span class="hljs-number">11</span>, <span class="hljs-number">69</span>);<br></code></pre></td></tr></table></figure>





<h3 id="6-分别采用UI界面和SQL语言为UNIVERSITY的表输入数据"><a href="#6-分别采用UI界面和SQL语言为UNIVERSITY的表输入数据" class="headerlink" title="6.分别采用UI界面和SQL语言为UNIVERSITY的表输入数据"></a>6.分别采用UI界面和SQL语言为UNIVERSITY的表输入数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `tc` (`Tno`, `Cno`, `Site`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>, <span class="hljs-number">1</span>, <span class="hljs-string">&#x27;教一楼407&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `tc` (`Tno`, `Cno`, `Site`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>, <span class="hljs-number">6</span>, <span class="hljs-string">&#x27;教一楼307&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `tc` (`Tno`, `Cno`, `Site`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2</span>, <span class="hljs-number">10</span>, <span class="hljs-string">&#x27;教二楼217&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `tc` (`Tno`, `Cno`, `Site`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">3</span>, <span class="hljs-number">2</span>, <span class="hljs-string">&#x27;教三楼507&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `tc` (`Tno`, `Cno`, `Site`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">4</span>, <span class="hljs-number">5</span>, <span class="hljs-string">&#x27;教三楼208&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `tc` (`Tno`, `Cno`, `Site`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">5</span>, <span class="hljs-number">9</span>, <span class="hljs-string">&#x27;教一楼207&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `tc` (`Tno`, `Cno`, `Site`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">6</span>, <span class="hljs-number">3</span>, <span class="hljs-string">&#x27;综合楼207&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> `tc` (`Tno`, `Cno`, `Site`) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">7</span>, <span class="hljs-number">4</span>, <span class="hljs-string">&#x27;教二楼817&#x27;</span>);<br></code></pre></td></tr></table></figure>




<h3 id="7-采用SQL语言查询所有的学生信息"><a href="#7-采用SQL语言查询所有的学生信息" class="headerlink" title="7.采用SQL语言查询所有的学生信息"></a>7.采用SQL语言查询所有的学生信息</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span><span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> Student;<br></code></pre></td></tr></table></figure>




<h3 id="8-采用SQL语言查询所有女生姓名"><a href="#8-采用SQL语言查询所有女生姓名" class="headerlink" title="8.采用SQL语言查询所有女生姓名"></a>8.采用SQL语言查询所有女生姓名</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span><span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> Student <span class="hljs-keyword">WHERE</span> Ssex <span class="hljs-operator">=</span> &quot;F&quot;;<br></code></pre></td></tr></table></figure>




<h3 id="9-采用SQL语言查询各个院系学生人数"><a href="#9-采用SQL语言查询各个院系学生人数" class="headerlink" title="9.采用SQL语言查询各个院系学生人数"></a>9.采用SQL语言查询各个院系学生人数</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-built_in">count</span>(Sno) TotalNum ,Dno <span class="hljs-keyword">FROM</span> Student <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> Dno;<br></code></pre></td></tr></table></figure>




<h3 id="10-采用SQL语言查询各个院系老师人数"><a href="#10-采用SQL语言查询各个院系老师人数" class="headerlink" title="10.采用SQL语言查询各个院系老师人数"></a>10.采用SQL语言查询各个院系老师人数</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-built_in">count</span>(Tno) TotalNum ,Dno <span class="hljs-keyword">FROM</span> Teacher <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> Dno;<br></code></pre></td></tr></table></figure>





<h3 id="11-采用SQL语言查询所有选修了数据库原理且成绩在60-100分之间的学生的姓名和成绩，并按照成绩的降序排列"><a href="#11-采用SQL语言查询所有选修了数据库原理且成绩在60-100分之间的学生的姓名和成绩，并按照成绩的降序排列" class="headerlink" title="11.采用SQL语言查询所有选修了数据库原理且成绩在60~100分之间的学生的姓名和成绩，并按照成绩的降序排列"></a>11.采用SQL语言查询所有选修了数据库原理且成绩在60~100分之间的学生的姓名和成绩，并按照成绩的降序排列</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span><br>	student.Sname,<br>	Grade <br><span class="hljs-keyword">FROM</span><br>	SC,<br>	Course,<br>	student <br><span class="hljs-keyword">WHERE</span><br>	SC.Cno <span class="hljs-operator">=</span> course.Cno <br>	<span class="hljs-keyword">AND</span> student.Sno <span class="hljs-operator">=</span> sc.Sno <br>	<span class="hljs-keyword">AND</span> course.Cname <span class="hljs-operator">=</span> &quot;数据库原理&quot; <br>	<span class="hljs-keyword">AND</span> SC.Grade <span class="hljs-keyword">BETWEEN</span> <span class="hljs-number">60</span> <span class="hljs-keyword">AND</span> <span class="hljs-number">100</span> <br><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span><br>	Grade <span class="hljs-keyword">DESC</span>;<br></code></pre></td></tr></table></figure>





<h3 id="12-采用SQL语言编写一个连接查询，查询经济管理学院年龄在20岁以下的男生的姓名和年龄"><a href="#12-采用SQL语言编写一个连接查询，查询经济管理学院年龄在20岁以下的男生的姓名和年龄" class="headerlink" title="12.采用SQL语言编写一个连接查询，查询经济管理学院年龄在20岁以下的男生的姓名和年龄"></a>12.采用SQL语言编写一个连接查询，查询经济管理学院年龄在20岁以下的男生的姓名和年龄</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span><br>	Sname,<br>	Sage <br><span class="hljs-keyword">FROM</span><br>	student,<br>	department <br><span class="hljs-keyword">WHERE</span><br>	student.Dno <span class="hljs-operator">=</span> department.Dno <br>	<span class="hljs-keyword">AND</span> department.Dname <span class="hljs-operator">=</span> &quot;经济管理学院&quot; <br>	<span class="hljs-keyword">AND</span> student.Sage <span class="hljs-operator">&lt;</span> <span class="hljs-number">20</span>;<br></code></pre></td></tr></table></figure>





<h3 id="13-采用SQL语言编写一个嵌套查询，查询选修课程总学分在5个学分以上的学生的姓名"><a href="#13-采用SQL语言编写一个嵌套查询，查询选修课程总学分在5个学分以上的学生的姓名" class="headerlink" title="13.采用SQL语言编写一个嵌套查询，查询选修课程总学分在5个学分以上的学生的姓名"></a>13.采用SQL语言编写一个嵌套查询，查询选修课程总学分在5个学分以上的学生的姓名</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <br><span class="hljs-keyword">FROM</span>(<br>	<span class="hljs-keyword">SELECT</span><br>		Sname,<br>		<span class="hljs-built_in">SUM</span>( CCredit ) Total_Credit <br>	<span class="hljs-keyword">FROM</span><br>		sc,<br>		course,<br>		student <br>	<span class="hljs-keyword">WHERE</span><br>		student.Sno <span class="hljs-operator">=</span> SC.Sno <br>		<span class="hljs-keyword">AND</span> SC.Cno <span class="hljs-operator">=</span> course.Cno <br>	<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span><br>		SC.Sno <br>	) <span class="hljs-keyword">AS</span> A <br><span class="hljs-keyword">WHERE</span><br>	Total_Credit <span class="hljs-operator">&gt;</span> <span class="hljs-number">5</span>;<br></code></pre></td></tr></table></figure>



<p><strong>不用嵌套，用HAVING 子句也可以</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span><br>	Sname,<br>	<span class="hljs-built_in">SUM</span>( CCredit ) Total_Credit <br><span class="hljs-keyword">FROM</span><br>	sc,<br>	course,<br>	student <br><span class="hljs-keyword">WHERE</span><br>	student.Sno <span class="hljs-operator">=</span> SC.Sno <br>	<span class="hljs-keyword">AND</span> SC.Cno <span class="hljs-operator">=</span> course.Cno <br><span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span><br>	SC.Sno <br><span class="hljs-keyword">HAVING</span><br>	<span class="hljs-built_in">SUM</span>( CCredit )<span class="hljs-operator">&gt;</span> <span class="hljs-number">5</span>;<br></code></pre></td></tr></table></figure>





<h3 id="14-采用SQL语言编写一个嵌套查询，查询各门课程的最高成绩的学生姓名及成绩"><a href="#14-采用SQL语言编写一个嵌套查询，查询各门课程的最高成绩的学生姓名及成绩" class="headerlink" title="14.采用SQL语言编写一个嵌套查询，查询各门课程的最高成绩的学生姓名及成绩"></a>14.采用SQL语言编写一个嵌套查询，查询各门课程的最高成绩的学生姓名及成绩</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span><br>	course.Cname,<br>	#course.cno,<br>	Sname,<br>	<span class="hljs-built_in">MAX</span>( Grade ) Max_Grade <br><span class="hljs-keyword">FROM</span><br>	sc,<br>	course,<br>	student <br><span class="hljs-keyword">WHERE</span><br>	student.Sno <span class="hljs-operator">=</span> SC.Sno <br>	<span class="hljs-keyword">AND</span> SC.Cno <span class="hljs-operator">=</span> course.Cno <br><span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span><br>	SC.Cno;<br></code></pre></td></tr></table></figure>




<h3 id="15-采用SQL语言查询所有选修了何小峰老师开设课程的学生姓名及其所在院系名称。"><a href="#15-采用SQL语言查询所有选修了何小峰老师开设课程的学生姓名及其所在院系名称。" class="headerlink" title="15.采用SQL语言查询所有选修了何小峰老师开设课程的学生姓名及其所在院系名称。"></a>15.采用SQL语言查询所有选修了何小峰老师开设课程的学生姓名及其所在院系名称。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span><br>	Sname,Dname<br><span class="hljs-keyword">FROM</span><br>	SC,<br>	student,<br>	department <br><span class="hljs-keyword">WHERE</span><br>	SC.Sno <span class="hljs-operator">=</span> student.Sno <br>	<span class="hljs-keyword">AND</span> student.Dno <span class="hljs-operator">=</span> department.Dno <br>	<span class="hljs-keyword">AND</span> SC.Cno <span class="hljs-keyword">IN</span> <br>	( <span class="hljs-keyword">SELECT</span> Cno <span class="hljs-keyword">FROM</span> teacher, course <br>	<span class="hljs-keyword">WHERE</span> teacher.Tno <span class="hljs-operator">=</span> course.Cpno <br>	<span class="hljs-keyword">AND</span> teacher.Tname <span class="hljs-operator">=</span> &quot;何小峰&quot; <br>	);<br></code></pre></td></tr></table></figure>





<h3 id="16-采用SQL语言，在数据库中删除学号为20091003085的学生的所有信息（包括其选课记录）"><a href="#16-采用SQL语言，在数据库中删除学号为20091003085的学生的所有信息（包括其选课记录）" class="headerlink" title="16.采用SQL语言，在数据库中删除学号为20091003085的学生的所有信息（包括其选课记录）"></a>16.采用SQL语言，在数据库中删除学号为20091003085的学生的所有信息（包括其选课记录）</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span>　<span class="hljs-keyword">FROM</span>　 SC,<br>Student　 <br><span class="hljs-keyword">WHERE</span><br>	SC.Sno <span class="hljs-operator">=</span> student.Sno <br>	<span class="hljs-keyword">AND</span> student.Sno <span class="hljs-operator">=</span> &quot;20091003085&quot;;<br></code></pre></td></tr></table></figure>




<h3 id="17-采用SQL语言，将学号为20091000863的学生的“数据库原理”这门课的成绩修改为80分。"><a href="#17-采用SQL语言，将学号为20091000863的学生的“数据库原理”这门课的成绩修改为80分。" class="headerlink" title="17.采用SQL语言，将学号为20091000863的学生的“数据库原理”这门课的成绩修改为80分。"></a>17.采用SQL语言，将学号为20091000863的学生的“数据库原理”这门课的成绩修改为80分。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">UPDATE</span> SC <br><span class="hljs-keyword">SET</span> grade <span class="hljs-operator">=</span> <span class="hljs-number">80</span> <br><span class="hljs-keyword">WHERE</span><br>	Sno <span class="hljs-operator">=</span> &quot;20091000863&quot; <br>	<span class="hljs-keyword">AND</span> Cno <span class="hljs-operator">=</span> ( <span class="hljs-keyword">SELECT</span> Cno <span class="hljs-keyword">FROM</span> COurse <span class="hljs-keyword">WHERE</span> Cname <span class="hljs-operator">=</span> &quot;数据库原理&quot; );<br></code></pre></td></tr></table></figure>




<hr>
<h2 id="视图的创建和使用"><a href="#视图的创建和使用" class="headerlink" title="视图的创建和使用"></a>视图的创建和使用</h2><h3 id="1-使用数据库管理系统的操作界面创建一个计算机学院的学生信息视图CSS，并新加入一条学生记录（20191000911，钟晓年，M，16）；然后修改该学生年龄为18岁，最后删除该视图。"><a href="#1-使用数据库管理系统的操作界面创建一个计算机学院的学生信息视图CSS，并新加入一条学生记录（20191000911，钟晓年，M，16）；然后修改该学生年龄为18岁，最后删除该视图。" class="headerlink" title="1.使用数据库管理系统的操作界面创建一个计算机学院的学生信息视图CSS，并新加入一条学生记录（20191000911，钟晓年，M，16）；然后修改该学生年龄为18岁，最后删除该视图。"></a>1.使用数据库管理系统的操作界面创建一个计算机学院的学生信息视图CSS，并新加入一条学生记录（20191000911，钟晓年，M，16）；然后修改该学生年龄为18岁，最后删除该视图。</h3><h3 id="2-采用SQL语言完成上一内容"><a href="#2-采用SQL语言完成上一内容" class="headerlink" title="2.采用SQL语言完成上一内容"></a>2.采用SQL语言完成上一内容</h3><h4 id="新建视图"><a href="#新建视图" class="headerlink" title="新建视图"></a>新建视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> CSS <span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span> Sno,Sname,Ssex,Sage <br><span class="hljs-keyword">FROM</span> Student <span class="hljs-keyword">WHERE</span> Dno <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;14&#x27;</span>;<br></code></pre></td></tr></table></figure>




<h4 id="插入数据"><a href="#插入数据" class="headerlink" title="插入数据"></a>插入数据</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> Css <span class="hljs-keyword">VALUES</span>( &quot;20191000911&quot;, &quot;钟晓年&quot;, <span class="hljs-string">&#x27;M&#x27;</span>, <span class="hljs-string">&#x27;16&#x27;</span> );<br></code></pre></td></tr></table></figure>



<p>实际操作发现Mysql在插入上述数据时，并没有将Dno设置为14，而是设置为了NULL，所以为了避免出错，将其手动修改为14</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">UPDATE</span> student <span class="hljs-keyword">SET</span> Dno <span class="hljs-operator">=</span> <span class="hljs-number">14</span> <span class="hljs-keyword">WHERE</span> Sno <span class="hljs-operator">=</span> <span class="hljs-number">20191000911</span><br></code></pre></td></tr></table></figure>


<h4 id="修改年龄"><a href="#修改年龄" class="headerlink" title="修改年龄"></a>修改年龄</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">UPDATE</span> CSS  <span class="hljs-keyword">SET</span> Sage <span class="hljs-operator">=</span> <span class="hljs-number">18</span> <span class="hljs-keyword">WHERE</span> Sno <span class="hljs-operator">=</span> <span class="hljs-number">20191000911</span><br></code></pre></td></tr></table></figure>



<h4 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> css <span class="hljs-keyword">WHERE</span> Sno <span class="hljs-operator">=</span> <span class="hljs-number">20191000911</span><br></code></pre></td></tr></table></figure>




<h4 id="删除视图"><a href="#删除视图" class="headerlink" title="删除视图"></a>删除视图</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">VIEW</span> CSS;<br></code></pre></td></tr></table></figure>




<h3 id="3-采用SQL语言创建一个计算机学院教师视图CST"><a href="#3-采用SQL语言创建一个计算机学院教师视图CST" class="headerlink" title="3.采用SQL语言创建一个计算机学院教师视图CST"></a>3.采用SQL语言创建一个计算机学院教师视图CST</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> CST <span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span><br>Tno,Tname,Ttitle <br><span class="hljs-keyword">FROM</span><br>	Teacher <br><span class="hljs-keyword">WHERE</span><br>	Dno <span class="hljs-operator">=</span> ( <span class="hljs-keyword">SELECT</span> Dno <span class="hljs-keyword">FROM</span> department <span class="hljs-keyword">WHERE</span> Dname <span class="hljs-operator">=</span> &quot;计算机学院&quot; );<br></code></pre></td></tr></table></figure>



<h3 id="4-采用SQL语言构建一个用到CSS和CST视图的查询，查询所有选修了计算机学院老师开设课程的计算机学院的学生姓名。"><a href="#4-采用SQL语言构建一个用到CSS和CST视图的查询，查询所有选修了计算机学院老师开设课程的计算机学院的学生姓名。" class="headerlink" title="4.采用SQL语言构建一个用到CSS和CST视图的查询，查询所有选修了计算机学院老师开设课程的计算机学院的学生姓名。"></a>4.采用SQL语言构建一个用到CSS和CST视图的查询，查询所有选修了计算机学院老师开设课程的计算机学院的学生姓名。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span><span class="hljs-comment">-- 3.最后在满足条件的学生中查找计算机学院的学生，返回姓名</span><br>Sname <br><span class="hljs-keyword">FROM</span> CSS,<br>	(<br>		<span class="hljs-keyword">SELECT</span><span class="hljs-comment">-- 2.再根据课程号找到选修了这些课程的学生</span><br>		<span class="hljs-operator">*</span> <br>	<span class="hljs-keyword">FROM</span><br>		sc <br>	<span class="hljs-keyword">WHERE</span><br>		cno <span class="hljs-keyword">IN</span> (<br>			<span class="hljs-keyword">SELECT</span><span class="hljs-comment">-- 1.先找到计算机学院老师开设的课程号</span><br>			cno <br>		<span class="hljs-keyword">FROM</span><br>			course <br>		<span class="hljs-keyword">WHERE</span><br>		Cpno <span class="hljs-keyword">IN</span> ( <span class="hljs-keyword">SELECT</span> Tno <span class="hljs-keyword">FROM</span> cst ))) <span class="hljs-keyword">AS</span> s <br><span class="hljs-keyword">WHERE</span><br>	s.sno <span class="hljs-operator">=</span> css.sno;<br></code></pre></td></tr></table></figure>




<h3 id="5-采用SQL语言写出在CST视图中删除教师编号为1-的记录，执行并观察结果。"><a href="#5-采用SQL语言写出在CST视图中删除教师编号为1-的记录，执行并观察结果。" class="headerlink" title="5.采用SQL语言写出在CST视图中删除教师编号为1 的记录，执行并观察结果。"></a>5.采用SQL语言写出在CST视图中删除教师编号为1 的记录，执行并观察结果。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> CST <span class="hljs-keyword">WHERE</span> Tno <span class="hljs-operator">=</span> <span class="hljs-number">1</span>;<br></code></pre></td></tr></table></figure>



<p>结果：不能直接删除编号为1的教师，因为TC表的Tno的外码是该列Tno。</p>
<hr>
<h2 id="安全性："><a href="#安全性：" class="headerlink" title="安全性："></a>安全性：</h2><h3 id="1-新建七个用户U1-U7，并将连接权限赋予这几个用户。"><a href="#1-新建七个用户U1-U7，并将连接权限赋予这几个用户。" class="headerlink" title="1.新建七个用户U1~U7，并将连接权限赋予这几个用户。"></a>1.新建七个用户U1~U7，并将连接权限赋予这几个用户。</h3><p>新建用户</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> U1 IDENTIFIED <span class="hljs-keyword">BY</span> &quot;test&quot;;<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> U2 IDENTIFIED <span class="hljs-keyword">BY</span> &quot;test&quot;;<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> U3 IDENTIFIED <span class="hljs-keyword">BY</span> &quot;test&quot;;<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> U4 IDENTIFIED <span class="hljs-keyword">BY</span> &quot;test&quot;;<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> U5 IDENTIFIED <span class="hljs-keyword">BY</span> &quot;test&quot;;<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> U6 IDENTIFIED <span class="hljs-keyword">BY</span> &quot;test&quot;;<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> U7 IDENTIFIED <span class="hljs-keyword">BY</span> &quot;test&quot;;<br></code></pre></td></tr></table></figure>



<p>查看用户是否创建成功</p>
<p>Mysql中没有赋予连接connect这种权限。</p>
<h3 id="2-将Student表的查询权限授予U1"><a href="#2-将Student表的查询权限授予U1" class="headerlink" title="2.将Student表的查询权限授予U1"></a>2.将Student表的查询权限授予U1</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> student <span class="hljs-keyword">TO</span> U1;<br></code></pre></td></tr></table></figure>






<h3 id="3-把Student表和Course表的全部权限授予U2和U3"><a href="#3-把Student表和Course表的全部权限授予U2和U3" class="headerlink" title="3.把Student表和Course表的全部权限授予U2和U3"></a>3.把Student表和Course表的全部权限授予U2和U3</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> Student <span class="hljs-keyword">TO</span> U2,U3;<br><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> Course  <span class="hljs-keyword">TO</span> U2,U3;<br></code></pre></td></tr></table></figure>




<h3 id="4-将SC表的查询权限授予所有用户"><a href="#4-将SC表的查询权限授予所有用户" class="headerlink" title="4.将SC表的查询权限授予所有用户"></a>4.将SC表的查询权限授予所有用户</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> sc <span class="hljs-keyword">TO</span> U1,U2,U3,U4,U5,U6,U7;<br></code></pre></td></tr></table></figure>




<h3 id="5-将SC表的更新和查询权限授予U4"><a href="#5-将SC表的更新和查询权限授予U4" class="headerlink" title="5.将SC表的更新和查询权限授予U4"></a>5.将SC表的更新和查询权限授予U4</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span>,<span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">ON</span> sc <span class="hljs-keyword">TO</span> U4;<br></code></pre></td></tr></table></figure>




<h3 id="6-将SC表的插入权限授予U5，并允许将此权限再授予其他用户"><a href="#6-将SC表的插入权限授予U5，并允许将此权限再授予其他用户" class="headerlink" title="6.将SC表的插入权限授予U5，并允许将此权限再授予其他用户"></a>6.将SC表的插入权限授予U5，并允许将此权限再授予其他用户</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">ON</span> SC <span class="hljs-keyword">TO</span> U5 <span class="hljs-keyword">WITH</span> <span class="hljs-keyword">GRANT</span> OPTION;<br></code></pre></td></tr></table></figure>




<h3 id="7-用U5连接数据库服务器，将SC表的插入权限授予U6，不允许U6再转授"><a href="#7-用U5连接数据库服务器，将SC表的插入权限授予U6，不允许U6再转授" class="headerlink" title="7.用U5连接数据库服务器，将SC表的插入权限授予U6，不允许U6再转授"></a>7.用U5连接数据库服务器，将SC表的插入权限授予U6，不允许U6再转授</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">ON</span> SC <span class="hljs-keyword">TO</span> U6;<br></code></pre></td></tr></table></figure>



<h3 id="8-用U6-连接数据库服务器，将SC表的插入权限授予U7，查看是否授权成功。"><a href="#8-用U6-连接数据库服务器，将SC表的插入权限授予U7，查看是否授权成功。" class="headerlink" title="8.用U6 连接数据库服务器，将SC表的插入权限授予U7，查看是否授权成功。"></a>8.用U6 连接数据库服务器，将SC表的插入权限授予U7，查看是否授权成功。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">ON</span> SC <span class="hljs-keyword">TO</span> U7;<br></code></pre></td></tr></table></figure>

<p>报错：</p>
<h3 id="9-收回U4修改SC表的权限"><a href="#9-收回U4修改SC表的权限" class="headerlink" title="9.收回U4修改SC表的权限"></a>9.收回U4修改SC表的权限</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">REVOKE</span> <span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">ON</span> SC <span class="hljs-keyword">FROM</span> U4;<br></code></pre></td></tr></table></figure>



<h3 id="10-收回所有用户对SC表的查询权限"><a href="#10-收回所有用户对SC表的查询权限" class="headerlink" title="10.收回所有用户对SC表的查询权限"></a>10.收回所有用户对SC表的查询权限</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">REVOKE</span> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">ON</span> SC <span class="hljs-keyword">FROM</span> U1,U2,U3,U4,U5,U6,U7;<br></code></pre></td></tr></table></figure>




<hr>
<h2 id="完整性："><a href="#完整性：" class="headerlink" title="完整性："></a>完整性：</h2><h3 id="1-在UNIVERSITY数据库中创建表STU-T-该表与Student有相同字段，主码为Sno。"><a href="#1-在UNIVERSITY数据库中创建表STU-T-该表与Student有相同字段，主码为Sno。" class="headerlink" title="1.在UNIVERSITY数据库中创建表STU_T,该表与Student有相同字段，主码为Sno。"></a>1.在UNIVERSITY数据库中创建表STU_T,该表与Student有相同字段，主码为Sno。</h3><h4 id="a-创建STU-T表时定义完整性（列级实体完整性），然后删除STU-T"><a href="#a-创建STU-T表时定义完整性（列级实体完整性），然后删除STU-T" class="headerlink" title="a.创建STU_T表时定义完整性（列级实体完整性），然后删除STU_T"></a>a.创建STU_T表时定义完整性（列级实体完整性），然后删除STU_T</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> STU_T ( Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ) <span class="hljs-keyword">PRIMARY</span> KEY, Sname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ), Ssex <span class="hljs-type">CHAR</span> ( <span class="hljs-number">2</span> ), Sage <span class="hljs-type">INT</span>, Dno <span class="hljs-type">INT</span> );<br></code></pre></td></tr></table></figure>


<p>删除</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> STU_T;<br></code></pre></td></tr></table></figure>


<h4 id="b-创建STU-T表时定义完整性（表级实体完整性），然后删除STU-T"><a href="#b-创建STU-T表时定义完整性（表级实体完整性），然后删除STU-T" class="headerlink" title="b.创建STU_T表时定义完整性（表级实体完整性），然后删除STU_T"></a>b.创建STU_T表时定义完整性（表级实体完整性），然后删除STU_T</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> STU_T (<br>	Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ),<br>	Sname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ),<br>	Ssex <span class="hljs-type">CHAR</span> ( <span class="hljs-number">2</span> ),<br>	Sage <span class="hljs-type">INT</span>,<br>	Dno <span class="hljs-type">INT</span>,<br>	<span class="hljs-keyword">PRIMARY</span> KEY ( Sno )<br>);<br></code></pre></td></tr></table></figure>


<p> 删除</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> STU_T;<br></code></pre></td></tr></table></figure>




<h4 id="c-创建STU-T表后再定义其实体完整性PK-SNO（提示：采用alter命令添加实体完整性）"><a href="#c-创建STU-T表后再定义其实体完整性PK-SNO（提示：采用alter命令添加实体完整性）" class="headerlink" title="c.创建STU_T表后再定义其实体完整性PK_SNO（提示：采用alter命令添加实体完整性）"></a>c.创建STU_T表后再定义其实体完整性PK_SNO（提示：采用alter命令添加实体完整性）</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> STU_T (<br>	Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ),<br>	Sname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ),<br>	Ssex <span class="hljs-type">CHAR</span> ( <span class="hljs-number">2</span> ),<br>	Sage <span class="hljs-type">INT</span>,<br>	Dno <span class="hljs-type">INT</span><br>);<br><br><br><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> stu_T <span class="hljs-keyword">ADD</span> <span class="hljs-keyword">CONSTRAINT</span> pk_sno <span class="hljs-keyword">PRIMARY</span> KEY ( Sno );<br></code></pre></td></tr></table></figure>


<h4 id="d-删除数据表STU-T中的实体完整性PK-SNO，然后删除STU-T"><a href="#d-删除数据表STU-T中的实体完整性PK-SNO，然后删除STU-T" class="headerlink" title="d.删除数据表STU_T中的实体完整性PK_SNO，然后删除STU_T"></a>d.删除数据表STU_T中的实体完整性PK_SNO，然后删除STU_T</h4><p>Mysql中不支持删除constraint</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> STU_T;<br></code></pre></td></tr></table></figure>





<h3 id="2-在UNIVERSITY数据库中创建表SC-T，该表与SC表具有相同字段，其主码为Sno，Cno"><a href="#2-在UNIVERSITY数据库中创建表SC-T，该表与SC表具有相同字段，其主码为Sno，Cno" class="headerlink" title="2.在UNIVERSITY数据库中创建表SC_T，该表与SC表具有相同字段，其主码为Sno，Cno"></a>2.在UNIVERSITY数据库中创建表SC_T，该表与SC表具有相同字段，其主码为Sno，Cno</h3><h4 id="a-创建S-T表时定义完整性（表级实体完整性），然后删除SC-T"><a href="#a-创建S-T表时定义完整性（表级实体完整性），然后删除SC-T" class="headerlink" title="a.创建S_T表时定义完整性（表级实体完整性），然后删除SC_T"></a>a.创建S_T表时定义完整性（表级实体完整性），然后删除SC_T</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> SC_T (<br>	Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ),<br>	Cno <span class="hljs-type">INT</span>,<br>	Grade <span class="hljs-type">INT</span>,<br>	<span class="hljs-keyword">PRIMARY</span> KEY ( Sno, Cno )<br>);<br></code></pre></td></tr></table></figure>


<h4 id="删除"><a href="#删除" class="headerlink" title="删除"></a>删除</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> SC_T;<br></code></pre></td></tr></table></figure>


<h4 id="b-创建SC-T表后再定义其实体完整性PK-SC（提示：采用alter命令添加实体完整性），然后删除数据表SC-T中的实体完整性PK-SC，并删除SC-T"><a href="#b-创建SC-T表后再定义其实体完整性PK-SC（提示：采用alter命令添加实体完整性），然后删除数据表SC-T中的实体完整性PK-SC，并删除SC-T" class="headerlink" title="b.创建SC_T表后再定义其实体完整性PK_SC（提示：采用alter命令添加实体完整性），然后删除数据表SC_T中的实体完整性PK_SC，并删除SC_T"></a>b.创建SC_T表后再定义其实体完整性PK_SC（提示：采用alter命令添加实体完整性），然后删除数据表SC_T中的实体完整性PK_SC，并删除SC_T</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> SC_T (<br>	Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ),<br>	Cno <span class="hljs-type">INT</span>,<br>	Grade <span class="hljs-type">INT</span><br>);<br><br><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> SC_T <span class="hljs-keyword">ADD</span> <span class="hljs-keyword">CONSTRAINT</span> pk_sc <span class="hljs-keyword">PRIMARY</span> KEY ( Sno, Cno );<br></code></pre></td></tr></table></figure>



<h4 id="删除-1"><a href="#删除-1" class="headerlink" title="删除"></a>删除</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> SC_T;<br></code></pre></td></tr></table></figure>




<h3 id="3-在UNIVERSITY数据库中创建表TC-T，该表与TC表具有相同字段，其主码为Tno，Cno"><a href="#3-在UNIVERSITY数据库中创建表TC-T，该表与TC表具有相同字段，其主码为Tno，Cno" class="headerlink" title="3.在UNIVERSITY数据库中创建表TC_T，该表与TC表具有相同字段，其主码为Tno，Cno"></a>3.在UNIVERSITY数据库中创建表TC_T，该表与TC表具有相同字段，其主码为Tno，Cno</h3><h4 id="a-创建TC-T表时定义实体完整性和参照完整性（采用表级实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno），然后删除TC-T表。"><a href="#a-创建TC-T表时定义实体完整性和参照完整性（采用表级实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno），然后删除TC-T表。" class="headerlink" title="a.创建TC_T表时定义实体完整性和参照完整性（采用表级实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno），然后删除TC_T表。"></a>a.创建TC_T表时定义实体完整性和参照完整性（采用表级实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno），然后删除TC_T表。</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> TC_T (<br>	Tno <span class="hljs-type">INT</span>,<br>	Cno <span class="hljs-type">INT</span>,<br>	Site <span class="hljs-type">CHAR</span> ( <span class="hljs-number">50</span> ),<br>	<span class="hljs-keyword">PRIMARY</span> KEY ( Tno, Cno )<br>);<br></code></pre></td></tr></table></figure>



<h4 id="删除-2"><a href="#删除-2" class="headerlink" title="删除"></a>删除</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> TC_T;<br></code></pre></td></tr></table></figure>



<h4 id="b-创建TC-T表后再定义其实体完整性和参照完整性（提示：采用alter命令增加实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno-。"><a href="#b-创建TC-T表后再定义其实体完整性和参照完整性（提示：采用alter命令增加实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno-。" class="headerlink" title="b.创建TC_T表后再定义其实体完整性和参照完整性（提示：采用alter命令增加实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno)。"></a>b.创建TC_T表后再定义其实体完整性和参照完整性（提示：采用alter命令增加实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno)。</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> TC_T (<br>	Tno <span class="hljs-type">INT</span>,<br>	Cno <span class="hljs-type">INT</span>,<br>	Site <span class="hljs-type">CHAR</span> ( <span class="hljs-number">50</span> )<br>);<br><br><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> TC_T <span class="hljs-keyword">add</span> <span class="hljs-keyword">CONSTRAINT</span> pk_a <span class="hljs-keyword">PRIMARY</span> KEY(Tno,Cno);<br><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> TC_T <span class="hljs-keyword">add</span> <span class="hljs-keyword">CONSTRAINT</span> pk_b <span class="hljs-keyword">FOREIGN</span> KEY(Tno) <span class="hljs-keyword">REFERENCES</span> teacher(Tno);<br><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> TC_T <span class="hljs-keyword">add</span> <span class="hljs-keyword">CONSTRAINT</span> pk_c <span class="hljs-keyword">FOREIGN</span> KEY(Cno) <span class="hljs-keyword">REFERENCES</span> course(Cno);<br></code></pre></td></tr></table></figure>



<h3 id="4-在UNIVERSITY数据库中创建表DEP-T，该表与Department表具有相同字段，其主码为Dno"><a href="#4-在UNIVERSITY数据库中创建表DEP-T，该表与Department表具有相同字段，其主码为Dno" class="headerlink" title="4.在UNIVERSITY数据库中创建表DEP_T，该表与Department表具有相同字段，其主码为Dno"></a>4.在UNIVERSITY数据库中创建表DEP_T，该表与Department表具有相同字段，其主码为Dno</h3><h4 id="a-创建DEP-T表时定义实体完整性（列级实体完整性），并定义Dname唯一且非空，Daddress的默认值为“北一楼”，然后删除DEP-T。"><a href="#a-创建DEP-T表时定义实体完整性（列级实体完整性），并定义Dname唯一且非空，Daddress的默认值为“北一楼”，然后删除DEP-T。" class="headerlink" title="a.创建DEP_T表时定义实体完整性（列级实体完整性），并定义Dname唯一且非空，Daddress的默认值为“北一楼”，然后删除DEP_T。"></a>a.创建DEP_T表时定义实体完整性（列级实体完整性），并定义Dname唯一且非空，Daddress的默认值为“北一楼”，然后删除DEP_T。</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> DEP_T<br> ( <br>Dno <span class="hljs-type">INT</span> <span class="hljs-keyword">PRIMARY</span> KEY, <br>Dname <span class="hljs-type">VARCHAR</span> ( <span class="hljs-number">50</span> ), <br>Daddress <span class="hljs-type">VARCHAR</span> ( <span class="hljs-number">50</span> ) <span class="hljs-keyword">DEFAULT</span> ( &quot;北一楼&quot; ) <br>);<br></code></pre></td></tr></table></figure>


<h4 id="删除-3"><a href="#删除-3" class="headerlink" title="删除"></a>删除</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> DEP_T;<br></code></pre></td></tr></table></figure>

<h4 id="b-创建DEP-T表时定义实体完整性（表级实体完整性），并限制Dno的取值范围为00-99（提示：使用Check进行约束），然后删除DEP-T。"><a href="#b-创建DEP-T表时定义实体完整性（表级实体完整性），并限制Dno的取值范围为00-99（提示：使用Check进行约束），然后删除DEP-T。" class="headerlink" title="b.创建DEP_T表时定义实体完整性（表级实体完整性），并限制Dno的取值范围为00~99（提示：使用Check进行约束），然后删除DEP_T。"></a>b.创建DEP_T表时定义实体完整性（表级实体完整性），并限制Dno的取值范围为00~99（提示：使用Check进行约束），然后删除DEP_T。</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> DEP_T<br> ( <br>Dno <span class="hljs-type">INT</span> , <br>Dname <span class="hljs-type">VARCHAR</span> ( <span class="hljs-number">50</span> ), <br>Daddress <span class="hljs-type">VARCHAR</span> ( <span class="hljs-number">50</span> ) , <br><span class="hljs-keyword">PRIMARY</span> KEY(Dno),<br><span class="hljs-keyword">check</span>(Dno<span class="hljs-operator">&lt;</span><span class="hljs-number">100</span> <span class="hljs-keyword">AND</span> Dno<span class="hljs-operator">&gt;</span><span class="hljs-number">0</span>)<br>);<br></code></pre></td></tr></table></figure>


<h2 id="数据库设计："><a href="#数据库设计：" class="headerlink" title="数据库设计："></a>数据库设计：</h2><p><strong>设计一个大学教学信息管理应用数据库UNIVERSITY。其中，一个教授属于一个系，一个系有多名教师，每个系都有自己的办公地点。每个教师可以讲授多门课程，每个学生属于一个系，可以选修多门课程。每门课程具有一定的学分，并可能有先导课程。</strong></p>
<h3 id="1-数据库概念结构设计"><a href="#1-数据库概念结构设计" class="headerlink" title="1.数据库概念结构设计"></a>1.数据库概念结构设计</h3><p>根据描述，识别出四个实体：教师、系、课程、学生<br>其中：教师与系是一对多的关系，教师与课程是一对多的关系。<br>系与学生是一对多的关系、学生与课程是多对多的关系<br>根据以上信息，可以画出E-R图：</p>
<h3 id="2-数据库逻辑结构"><a href="#2-数据库逻辑结构" class="headerlink" title="2.数据库逻辑结构"></a>2.数据库逻辑结构</h3><p>根据E-R图以及概念结构向逻辑结构转换规则，设计逻辑结构如下：</p>
<p>UNIVERSITY关系数据库模式：</p>
<blockquote>
<p>系的信息表Department(Dno,Dname,Daddress)<br>学生信息表Student(Sno,Sname,Ssex,Sage,Dno)<br>教师信息表Teacher(Tno,Tname,Ttitlt,Dno) 课程信息表Course(Cno,Cname,Cpno,Ccredit)<br>选课信息表SC(Sno,Cno,Grade) 授课信息表TC(Tno,Cno,Site)</p>
</blockquote>
<h3 id="3-数据库物理结构设计"><a href="#3-数据库物理结构设计" class="headerlink" title="3.数据库物理结构设计"></a>3.数据库物理结构设计</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Department ( Dno <span class="hljs-type">INT</span>, Dname <span class="hljs-type">VARCHAR</span> ( <span class="hljs-number">50</span> ), Daddress <span class="hljs-type">VARCHAR</span> ( <span class="hljs-number">50</span> ), <span class="hljs-keyword">PRIMARY</span> KEY ( Dno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Student (Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ),Sname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ),Ssex <span class="hljs-type">CHAR</span> ( <span class="hljs-number">2</span> ),Sage <span class="hljs-type">INT</span>,Dno <span class="hljs-type">INT</span>,<span class="hljs-keyword">PRIMARY</span> KEY ( Sno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Dno ) <span class="hljs-keyword">REFERENCES</span> department ( Dno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Teacher (Tno <span class="hljs-type">INT</span> <span class="hljs-keyword">PRIMARY</span> KEY,Tname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ),Ttitle <span class="hljs-type">CHAR</span> ( <span class="hljs-number">8</span> ),Dno <span class="hljs-type">INT</span>,<span class="hljs-keyword">FOREIGN</span> KEY ( Dno ) <span class="hljs-keyword">REFERENCES</span> department ( Dno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> course ( Cno <span class="hljs-type">INT</span> <span class="hljs-keyword">PRIMARY</span> KEY, Cname <span class="hljs-type">CHAR</span> ( <span class="hljs-number">50</span> ), Cpno <span class="hljs-type">INT</span>, CCredit <span class="hljs-type">INT</span>, <span class="hljs-keyword">FOREIGN</span> KEY ( Cpno ) <span class="hljs-keyword">REFERENCES</span> Course ( Cno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> SC (Sno <span class="hljs-type">CHAR</span> ( <span class="hljs-number">11</span> ),Cno <span class="hljs-type">INT</span>,Grade <span class="hljs-type">INT</span>,<span class="hljs-keyword">PRIMARY</span> KEY ( Sno, Cno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Sno ) <span class="hljs-keyword">REFERENCES</span> Student ( Sno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Cno )<span class="hljs-keyword">REFERENCES</span> Course ( Cno ) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> TC (Tno <span class="hljs-type">INT</span>,Cno <span class="hljs-type">INT</span>,Site <span class="hljs-type">CHAR</span> ( <span class="hljs-number">50</span> ),<span class="hljs-keyword">PRIMARY</span> KEY ( Tno, Cno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Tno ) <span class="hljs-keyword">REFERENCES</span> Teacher ( Tno ),<span class="hljs-keyword">FOREIGN</span> KEY ( Cno ) <span class="hljs-keyword">REFERENCES</span> Course ( Cno ) );<br></code></pre></td></tr></table></figure>








<h2 id="存储过程与函数："><a href="#存储过程与函数：" class="headerlink" title="存储过程与函数："></a>存储过程与函数：</h2><h3 id="1-定义一个无参数的存储过程DecreaseGrade，更新所有学生成绩，将其降低5-，并调用该存储过程。"><a href="#1-定义一个无参数的存储过程DecreaseGrade，更新所有学生成绩，将其降低5-，并调用该存储过程。" class="headerlink" title="1.定义一个无参数的存储过程DecreaseGrade，更新所有学生成绩，将其降低5%，并调用该存储过程。"></a>1.定义一个无参数的存储过程DecreaseGrade，更新所有学生成绩，将其降低5%，并调用该存储过程。</h3><p>命令行客户端中，如果有一行命令以delimiter( 默认是分号; )结束，那么回车后，mysql将会执行该命令。为了避免语句在中途被执行需要 事先把delimiter换成其它符号，这里换成了 ￥￥</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql">DELIMITER $<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> DecreaseGrade()<br><span class="hljs-keyword">BEGIN</span> <br><span class="hljs-keyword">update</span> SC <span class="hljs-keyword">set</span> Grade<span class="hljs-operator">=</span>Grade<span class="hljs-operator">*</span><span class="hljs-number">0.95</span>;<br><span class="hljs-keyword">END</span><br>$<br>DELIMITER ;<br></code></pre></td></tr></table></figure>


<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CALL</span> DecreaseGrade;<br></code></pre></td></tr></table></figure>




<h3 id="2-定义一个带输入参数的存储过程IncreaseGrade，将课程号为1的所有学生成绩提升5-；要求课程号作为存储过程参数传入，并调用该存储过程。"><a href="#2-定义一个带输入参数的存储过程IncreaseGrade，将课程号为1的所有学生成绩提升5-；要求课程号作为存储过程参数传入，并调用该存储过程。" class="headerlink" title="2.定义一个带输入参数的存储过程IncreaseGrade，将课程号为1的所有学生成绩提升5%；要求课程号作为存储过程参数传入，并调用该存储过程。"></a>2.定义一个带输入参数的存储过程IncreaseGrade，将课程号为1的所有学生成绩提升5%；要求课程号作为存储过程参数传入，并调用该存储过程。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs sql">DELIMITER $<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> IncreaseGrade(<span class="hljs-keyword">IN</span> ccno <span class="hljs-type">INT</span>)<br><span class="hljs-keyword">BEGIN</span> <br><span class="hljs-keyword">update</span> SC <span class="hljs-keyword">set</span> Grade<span class="hljs-operator">=</span>Grade<span class="hljs-operator">*</span><span class="hljs-number">1.05</span> <span class="hljs-keyword">WHERE</span> Cno<span class="hljs-operator">=</span>ccno;<br><span class="hljs-keyword">END</span><br>$<br>DELIMITER ;<br><br><br><span class="hljs-keyword">CALL</span> IncreaseGrade(<span class="hljs-number">1</span>)<br></code></pre></td></tr></table></figure>




<h3 id="3-定义一个带有输入和输出参数的存储过程AverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，以计算结果（该生的所有选修课平均成绩）为输出参数，调用该存储过程，并输出计算结果。"><a href="#3-定义一个带有输入和输出参数的存储过程AverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，以计算结果（该生的所有选修课平均成绩）为输出参数，调用该存储过程，并输出计算结果。" class="headerlink" title="3.定义一个带有输入和输出参数的存储过程AverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，以计算结果（该生的所有选修课平均成绩）为输出参数，调用该存储过程，并输出计算结果。"></a>3.定义一个带有输入和输出参数的存储过程AverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，以计算结果（该生的所有选修课平均成绩）为输出参数，调用该存储过程，并输出计算结果。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><code class="hljs sql">DELIMITER $<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> AverageStudentGrade(<span class="hljs-keyword">IN</span> xno <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">20</span>),<span class="hljs-keyword">OUT</span> xgrade <span class="hljs-type">FlOAT</span>)<br><span class="hljs-keyword">BEGIN</span> <br><span class="hljs-keyword">DECLARE</span> g <span class="hljs-type">FLOAT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0.0</span>;<br><span class="hljs-keyword">SELECT</span> sg.ag <span class="hljs-keyword">INTO</span> g <br><span class="hljs-keyword">FROM</span> (<br>    <span class="hljs-keyword">SELECT</span> Sno s,<span class="hljs-built_in">AVG</span>(Grade) ag<br>    <span class="hljs-keyword">FROM</span> SC <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> Sno<br>)sg<br><span class="hljs-keyword">WHERE</span> sg.g <span class="hljs-operator">=</span> xno;<br><span class="hljs-keyword">END</span><br>$<br>DELIMITER ;<br></code></pre></td></tr></table></figure>



<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CALL</span> AverageStudentGrade(<span class="hljs-number">20091000863</span>,<span class="hljs-variable">@g</span>);<br></code></pre></td></tr></table></figure>


<h3 id="4-删除过程IncreaseGrade和DecreaseGrade。"><a href="#4-删除过程IncreaseGrade和DecreaseGrade。" class="headerlink" title="4.删除过程IncreaseGrade和DecreaseGrade。"></a>4.删除过程IncreaseGrade和DecreaseGrade。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">PROCEDURE</span> DecreaseGrade;<br><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">PROCEDURE</span> IncreaseGrade;<br></code></pre></td></tr></table></figure>



<h3 id="5-定义一个带有输入参数的自定义函数CalculateAverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，返回该生的所有选修课平均成绩，调用函数，并输出计算结果。"><a href="#5-定义一个带有输入参数的自定义函数CalculateAverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，返回该生的所有选修课平均成绩，调用函数，并输出计算结果。" class="headerlink" title="5.定义一个带有输入参数的自定义函数CalculateAverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，返回该生的所有选修课平均成绩，调用函数，并输出计算结果。"></a>5.定义一个带有输入参数的自定义函数CalculateAverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，返回该生的所有选修课平均成绩，调用函数，并输出计算结果。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><code class="hljs sql">DELIMITER $<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">FUNCTION</span> CalculateAverageStudentGrade(xno <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">11</span>))<br><span class="hljs-keyword">RETURNS</span> <span class="hljs-type">INT</span> <br><span class="hljs-keyword">BEGIN</span> <br>    <span class="hljs-keyword">DECLARE</span> g <span class="hljs-type">INT</span>;<br>    <span class="hljs-keyword">SELECT</span> sg.ag <span class="hljs-keyword">INTO</span> g <br>    <span class="hljs-keyword">FROM</span>(<br>        <span class="hljs-keyword">SELECT</span> Sno s,<span class="hljs-built_in">AVG</span>(Grade) ag<br>        <span class="hljs-keyword">FROM</span> SC <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> Sno   <br>    )sg<br>    <span class="hljs-keyword">WHERE</span> sg.g<span class="hljs-operator">=</span>xno;<br>    <span class="hljs-keyword">RETURN</span> g;<br><span class="hljs-keyword">END</span><br>$<br>DELIMITER ;<br></code></pre></td></tr></table></figure>


<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CALL</span> CalculateAverageStudentGrade(<span class="hljs-string">&#x27;20091000863&#x27;</span>)<br></code></pre></td></tr></table></figure>


<h3 id="6-删除函数CalculateAverageStudentGrade"><a href="#6-删除函数CalculateAverageStudentGrade" class="headerlink" title="6.删除函数CalculateAverageStudentGrade"></a>6.删除函数CalculateAverageStudentGrade</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">FUNCTION</span> CalculateAverageStudentGrade;<br></code></pre></td></tr></table></figure>





<h3 id="7-定义一个存储过程，采用普通无参游标实现计算机学院开设的所有课程的学分之和。"><a href="#7-定义一个存储过程，采用普通无参游标实现计算机学院开设的所有课程的学分之和。" class="headerlink" title="7.定义一个存储过程，采用普通无参游标实现计算机学院开设的所有课程的学分之和。"></a>7.定义一个存储过程，采用普通无参游标实现计算机学院开设的所有课程的学分之和。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><code class="hljs sql">DELIMITER $<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> CS_Total_Credit()<br><span class="hljs-keyword">BEGIN</span> <br><span class="hljs-keyword">DECLARE</span> credit <span class="hljs-type">INT</span>;<br><span class="hljs-keyword">DECLARE</span> credit_sum <span class="hljs-type">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">FALSE</span>;<br><span class="hljs-keyword">DECLARE</span> done <span class="hljs-type">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">FALSE</span>;<br><br><span class="hljs-keyword">DECLARE</span> cur <span class="hljs-keyword">CURSOR</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">SELECT</span> CCredit <span class="hljs-keyword">FROM</span> Course;<br><span class="hljs-keyword">DECLARE</span> continue HANDLER <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">NOT</span> found <span class="hljs-keyword">SET</span> done <span class="hljs-operator">=</span> <span class="hljs-literal">TRUE</span>;<br><span class="hljs-keyword">OPEN</span> cur;<br>    read_loop:loop<br>        <span class="hljs-keyword">FETCH</span> cur <span class="hljs-keyword">INTO</span> credit;<br>        IF done <span class="hljs-keyword">THEN</span> <br>            LEAVE read_loop;<br>        <span class="hljs-keyword">END</span> IF;<br>        <span class="hljs-keyword">SET</span> credit_sum<span class="hljs-operator">=</span>credit_sum<span class="hljs-operator">+</span>credit;<br>    <span class="hljs-keyword">END</span> loop;<br><span class="hljs-keyword">CLOSE</span> cur;<br><span class="hljs-keyword">SELECT</span> credit_sum;<br><span class="hljs-keyword">END</span><br>$<br>DELIMITER ;<br><br><br><br><span class="hljs-keyword">CALL</span> CS_Total_Credit();<br></code></pre></td></tr></table></figure>



<h3 id="8-定义一个存储过程，采用REF-CURSOR实现计算机学院所有学生选修课程的成绩之和。"><a href="#8-定义一个存储过程，采用REF-CURSOR实现计算机学院所有学生选修课程的成绩之和。" class="headerlink" title="8.定义一个存储过程，采用REF CURSOR实现计算机学院所有学生选修课程的成绩之和。"></a>8.定义一个存储过程，采用REF CURSOR实现计算机学院所有学生选修课程的成绩之和。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><code class="hljs sql">DELIMITER $<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> CS_Total_Stu_Credit()<br><span class="hljs-keyword">BEGIN</span> <br><span class="hljs-keyword">DECLARE</span> grade <span class="hljs-type">int</span>;<br><span class="hljs-keyword">DECLARE</span> grade_sum <span class="hljs-type">int</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;<br><span class="hljs-keyword">DECLARE</span> done <span class="hljs-type">int</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">FALSE</span>;<br><span class="hljs-keyword">DECLARE</span> cur_grade <span class="hljs-keyword">CURSOR</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">SELECT</span> Grade <span class="hljs-keyword">FROM</span> SC;<br><span class="hljs-keyword">DECLARE</span> CONTINUE HANDLER <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">NOT</span> found <span class="hljs-keyword">SET</span> done<span class="hljs-operator">=</span><span class="hljs-literal">TRUE</span>;<br><span class="hljs-keyword">OPEN</span> cur_grade;<br>read_loop:LOOP<br>    <span class="hljs-keyword">FETCH</span> cur_grade <span class="hljs-keyword">INTO</span> grade;<br>    IF done <span class="hljs-keyword">THEN</span> <br>        LEAVE read_loop;<br>    <span class="hljs-keyword">END</span> IF;<br>    <span class="hljs-keyword">SET</span> grade_sum <span class="hljs-operator">=</span> grade_sum<span class="hljs-operator">+</span>grade;<br><span class="hljs-keyword">END</span> LOOP;<br><span class="hljs-keyword">CLOSE</span> cur_grade;<br><span class="hljs-keyword">SELECT</span> grade_sum;<br><span class="hljs-keyword">END</span><br>$<br>DELIMITER ;<br><br><br><span class="hljs-keyword">CALL</span> CS_Total_Stu_Credit();<br></code></pre></td></tr></table></figure>



<h3 id="9-定义一个存储过程，采用带参数的游标实现按照学号计算学生的平均成绩。"><a href="#9-定义一个存储过程，采用带参数的游标实现按照学号计算学生的平均成绩。" class="headerlink" title="9.定义一个存储过程，采用带参数的游标实现按照学号计算学生的平均成绩。"></a>9.定义一个存储过程，采用带参数的游标实现按照学号计算学生的平均成绩。</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><code class="hljs sql">DELIMITER $<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> average_grade(<span class="hljs-keyword">IN</span> sno_in <span class="hljs-type">char</span>(<span class="hljs-number">11</span>))<br><span class="hljs-keyword">BEGIN</span><br><span class="hljs-keyword">DECLARE</span> avg_grade <span class="hljs-type">int</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;<br><span class="hljs-keyword">DECLARE</span> done <span class="hljs-type">BOOLEAN</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">FALSE</span>;<br><span class="hljs-keyword">DECLARE</span> cur <span class="hljs-keyword">CURSOR</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">SELECT</span> sg.ag<br>    <span class="hljs-keyword">FROM</span><br>        (<span class="hljs-keyword">SELECT</span> Sno s,<span class="hljs-built_in">avg</span>(Grade) ag<br>        <span class="hljs-keyword">FROM</span> SC <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> Sno )sg<br>    <span class="hljs-keyword">WHERE</span> sg.s <span class="hljs-operator">=</span> sno_in;<br><span class="hljs-keyword">DECLARE</span> CONTINUE HANDLER <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">NOT</span> found <span class="hljs-keyword">SET</span> done<span class="hljs-operator">=</span><span class="hljs-literal">TRUE</span>;<br><span class="hljs-keyword">OPEN</span> cur;<br>read_loop:LOOP<br>    <span class="hljs-keyword">FETCH</span> cur <span class="hljs-keyword">INTO</span> avg_grade;<br>    IF done <span class="hljs-keyword">THEN</span><br>        LEAVE read_loop;<br>    <span class="hljs-keyword">END</span> IF;<br>    <span class="hljs-keyword">SELECT</span> avg_grade;<br><span class="hljs-keyword">END</span> LOOP;<br><span class="hljs-keyword">CLOSE</span> cur;<br><br><span class="hljs-keyword">END</span><br>$<br>DELIMITER ;<br></code></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CALL</span> average_grade(<span class="hljs-string">&#x27;20091000863&#x27;</span>);<br></code></pre></td></tr></table></figure>
















            </div>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
                    
                      <a class="hover-with-bg" href="/tags/SQL/">SQL</a>
                    
                      <a class="hover-with-bg" href="/tags/%E7%BB%83%E4%B9%A0/">练习</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">
                  
                    本博客所有文章除特别声明外，均采用 <a target="_blank" href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！
                  
                </p>
              
              
                <div class="post-prevnext">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/2021/06/02/%E6%95%B0%E6%8D%AE%E5%BA%93__%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AC%94%E8%AE%B0--SQL%E9%83%A8%E5%88%86/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">数据库笔记--SQL部分</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/2021/05/10/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F__%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F%E5%A4%8D%E4%B9%A0%E7%AC%94%E8%AE%B0/">
                        <span class="hidden-mobile">操作系统复习笔记</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
          </article>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    

    
      <a id="scroll-top-button" aria-label="TOP" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
    

    
  </main>

  <footer class="text-center mt-5 py-3">
  <div class="footer-content">
     <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a> <i class="iconfont icon-love"></i> <a href="https://cheney822.gitee.io/" target="_blank" rel="nofollow noopener"><span>备用网址</span></a> 
  </div>
  

  
  <!-- 备案信息 -->
  <div class="beian">
    <span>
      <a href="http://beian.miit.gov.cn/" target="_blank" rel="nofollow noopener">
        皖ICP备2022002876号-1
      </a>
    </span>
    
  </div>


  
</footer>


  <!-- SCRIPTS -->
  
  <script  src="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":false,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js" ></script>
<script  src="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/js/bootstrap.min.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>

<!-- Plugins -->


  <script  src="/js/local-search.js" ></script>



  
    <script  src="/js/img-lazyload.js" ></script>
  



  



  
    <script  src="https://cdn.jsdelivr.net/npm/tocbot@4/dist/tocbot.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/anchor-js@4/anchor.min.js" ></script>
  
  
    <script defer src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js" ></script>
  






  <script  src="https://cdn.jsdelivr.net/npm/typed.js@2/lib/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var title = document.getElementById('subtitle').title;
      
        typing(title);
      
    })(window, document);
  </script>















<!-- 主题的启动项 保持在最底部 -->
<script  src="/js/boot.js" ></script>


</body>
</html>
